USE [eShop]
GO

/****** Object:  StoredProcedure [dbo].[USP_Profits_Query]    Script Date: 10/19/2011 06:49:52 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Profits_Query]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_Profits_Query]
GO

USE [eShop]
GO

/****** Object:  StoredProcedure [dbo].[USP_Profits_Query]    Script Date: 10/19/2011 06:49:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_Profits_Query]
@Type	INT=0,--0,年;1,月;2,日;3,单号;4,具体物品
@Year	VARCHAR(4)=NULL,
@Month	VARCHAR(2)=NULL,
@Day	VARCHAR(2)=NULL,
@OrderID VARCHAR(50)=NULL
AS
--项目，销售额，成本，利润
--Items,RealAmount,BuyCost,Profits
--By Year
IF @Type=0
SELECT SellTime Items,SUM(RealAmount) RealAmount,SUM(BuyCost) BuyCost,SUM((RealAmount-BuyCost)) Profits FROM (
	select YEAR(o.SellTime) SellTime, O.OrderID,O.MemberID,MB.MemberName,CONVERT(DECIMAL(12,2),
				O.RealAmount) RealAmount,CONVERT(DECIMAL(12,2),SUM(S.UnitPrice*OD.BuyNumber)) BuyCost 
		from T_Merchandise_Order O,T_Merchandise_OrderDetail OD,T_Stock_In S,T_Member_BasicInfo MB
			WHERE O.OrderID=OD.OrderID
			AND OD.StockInOrder=S.StockInOrder
			AND O.MemberID=MB.MemberID
			GROUP BY YEAR(O.SellTime),O.OrderID,O.MemberID,O.RealAmount,MB.MemberName
	 ) BaseData
	 GROUP BY SellTime
--By Month
IF @Type=1
SELECT SellTime Items,SUM(RealAmount) RealAmount,SUM(BuyCost) BuyCost,SUM((RealAmount-BuyCost)) Profits FROM (
	SELECT O.OrderID,MONTH(o.SellTime) SellTime, 
			CONVERT(DECIMAL(12,2),O.RealAmount) RealAmount,
			CONVERT(DECIMAL(12,2),SUM(S.UnitPrice*OD.BuyNumber)) BuyCost 
		from T_Merchandise_Order O,T_Merchandise_OrderDetail OD,T_Stock_In S,T_Member_BasicInfo MB
			WHERE O.OrderID=OD.OrderID
			AND OD.StockInOrder=S.StockInOrder
			AND O.MemberID=MB.MemberID
			AND YEAR(O.SellTime)=@Year
			GROUP BY O.OrderID,MONTH(O.SellTime),CONVERT(DECIMAL(12,2),O.RealAmount)
	 ) BaseData
	 GROUP BY SellTime 
--By Day
IF @Type=2
SELECT SellTime Items,SUM(RealAmount) RealAmount,SUM(BuyCost) BuyCost,SUM((RealAmount-BuyCost)) Profits FROM (
	SELECT O.OrderID,day(o.SellTime) SellTime, 
			CONVERT(DECIMAL(12,2),O.RealAmount) RealAmount,
			CONVERT(DECIMAL(12,2),SUM(S.UnitPrice*OD.BuyNumber)) BuyCost 
		from T_Merchandise_Order O,T_Merchandise_OrderDetail OD,T_Stock_In S,T_Member_BasicInfo MB
			WHERE O.OrderID=OD.OrderID
			AND OD.StockInOrder=S.StockInOrder
			AND O.MemberID=MB.MemberID
			AND YEAR(O.SellTime)=@Year
			AND MONTH(O.SellTime)=@Month
			GROUP BY O.OrderID,day(O.SellTime),CONVERT(DECIMAL(12,2),O.RealAmount)
	 ) BaseData
	 GROUP BY SellTime 
--By OrderID
IF @Type=3
SELECT OrderID Items,SUM(RealAmount) RealAmount,SUM(BuyCost) BuyCost,SUM((RealAmount-BuyCost)) Profits FROM (
	SELECT O.OrderID,day(o.SellTime) SellTime, 
			CONVERT(DECIMAL(12,2),O.RealAmount) RealAmount,
			CONVERT(DECIMAL(12,2),SUM(S.UnitPrice*OD.BuyNumber)) BuyCost 
		from T_Merchandise_Order O,T_Merchandise_OrderDetail OD,T_Stock_In S,T_Member_BasicInfo MB
			WHERE O.OrderID=OD.OrderID
			AND OD.StockInOrder=S.StockInOrder
			AND O.MemberID=MB.MemberID
			AND YEAR(O.SellTime)=@Year
			AND MONTH(O.SellTime)=@Month
			AND DAY(O.SellTime)=@Day
			GROUP BY O.OrderID,day(O.SellTime),CONVERT(DECIMAL(12,2),O.RealAmount)
	 ) BaseData
	 GROUP BY OrderID 
IF @Type=4
SELECT OD.OrderID,OD.Barcode,B.MerchandiseName,U.UnitName,SUM(OD.BuyNumber) BuyNumber FROM T_Merchandise_OrderDetail OD,T_Merchandise_BasicInfo B,T_Merchandise_Unit U 
WHERE OD.OrderID IN(SELECT OrderID FROM T_Merchandise_Order)
AND OD.Barcode=B.Barcode
AND B.MerchandiseUnitID=U.UnitID
AND OD.OrderID=@OrderID
GROUP BY OD.OrderID,OD.Barcode,B.MerchandiseName,U.UnitName
ORDER BY OrderID
GO

